﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class DataProvider
    Private _strConnectionString As String = "Data Source=ThanhDuc\Admin;Initial Catalog=DBNGK;Integrated Security=True;User ID=;Password="
    Private _connection As SqlConnection
    Private _error As String

    Public Property ErrorProperty() As String
        Get
            Return _error
        End Get
        Set(ByVal value As String)
            _error = value
        End Set
    End Property

    Public Function OpenConnect() As Boolean
         Try
            If _connection Is Nothing Then
                _connection = New SqlConnection(_strConnectionString)
            End If
            If _connection.State = ConnectionState.Open Then
                _connection.Close()
            End If
            _connection.Open()
            Return True
        Catch ex As SqlException
            ErrorProperty = ex.Message
        End Try
        Return False
    End Function

    Public Function CloseConnect() As Boolean
        Try
            If _connection IsNot Nothing Then
                If _connection.State = ConnectionState.Open Then
                    _connection.Close()
                End If
            End If
            Return True
        Catch ex As Exception
            ErrorProperty = ex.Message
            Return False
        End Try
    End Function

    Public Function ExecuteNonQuery(ByVal procName As String, ByVal ParamArray param As SqlParameter()) As Boolean
        Try
            If OpenConnect() Then
                Dim sqlCommand As SqlCommand = _connection.CreateCommand()
                sqlCommand.CommandType = CommandType.StoredProcedure
                sqlCommand.CommandText = procName
                If param IsNot Nothing Then
                    sqlCommand.Parameters.AddRange(param)
                End If
                sqlCommand.ExecuteNonQuery()
                ErrorProperty = ""
                Return True
            End If
            Return False
        Catch ex As SqlException
            _error = ex.Message
            Return False
        Finally
            CloseConnect()
        End Try
    End Function

    'Execute Query Command text
    Public Function ExecuteQuery(ByVal procName As String, ByVal ParamArray para As SqlParameter()) As DataTable
        Dim dt As DataTable = Nothing
        Try
            If OpenConnect() Then
                Dim sqlCommand As SqlCommand = _connection.CreateCommand()
                sqlCommand.CommandType = CommandType.StoredProcedure
                sqlCommand.CommandText = procName
                If para IsNot Nothing Then
                    sqlCommand.Parameters.AddRange(para)
                End If
                Dim dbadapter As SqlDataAdapter = New SqlDataAdapter(sqlCommand)
                dt = New DataTable()
                dbadapter.Fill(dt)
                ErrorProperty = ""
            End If
        Catch ex As SqlException
            ErrorProperty = ex.Message
        Finally
            CloseConnect()
        End Try
        Return dt
    End Function

    'Execute Query Command Store Procedure
    Public Function ExecuteQuerys(ByVal procName As String, ByVal ParamArray para As SqlParameter()) As Object

        Dim value As Object = Nothing
        Try
            If OpenConnect() Then
                Dim sqlCommand As SqlCommand = _connection.CreateCommand()
                sqlCommand.CommandType = CommandType.StoredProcedure
                sqlCommand.CommandText = procName
                If para IsNot Nothing Then
                    sqlCommand.Parameters.AddRange(para)
                End If
                value = sqlCommand.ExecuteScalar()
                ErrorProperty = ""
                sqlCommand.Parameters.Clear()
            End If
        Catch ex As SqlException
            ErrorProperty = ex.Message
        Finally
            CloseConnect()
        End Try
        Return value
    End Function

    Public Sub Backup(ByVal filename As String)
        Try
            If OpenConnect() Then
                Dim sqlCommand As SqlCommand = _connection.CreateCommand()
                sqlCommand.CommandText = "backup database " & _connection.Database.ToString & " to disk='" & filename & "'"
                sqlCommand.ExecuteNonQuery()
                ErrorProperty = ""
            End If
        Catch ex As SqlException
            ErrorProperty = ex.Message
        Finally
            CloseConnect()
        End Try
    End Sub
    Public Sub restore(ByVal filename As String)
        Try
            If OpenConnect() Then
                Dim sqlCommand As SqlCommand = _connection.CreateCommand()
                sqlCommand.CommandText = "USE MASTER ALTER DATABASE " + _connection.Database + " SET Single_User WITH Rollback Immediate ALTER DATABASE " + _connection.Database + " SET Multi_User RESTORE DATABASE " + _connection.Database + " FROM DISK = '" + filename + "' WITH REPLACE"
                sqlCommand.ExecuteNonQuery()
                ErrorProperty = ""
            End If
        Catch ex As SqlException
            ErrorProperty = ex.Message
        Finally
            CloseConnect()
        End Try
    End Sub
End Class

